package com.dminvoicemanager.backup;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import com.dminvoicemanager.DM_InvoiceMenu;
import com.dminvoicemanager.R;
import android.app.Activity;
import android.app.AlertDialog;
import android.app.Dialog;
import android.content.Context;
import android.content.Intent;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.os.Environment;
import android.util.Log;
import android.view.LayoutInflater;
import android.view.View;
import android.widget.TextView;

public class DM_Stock_Adjustment_Backup extends Activity{
	private static final String TAG = null;
	String pdfFileName;
	private String DBNAME = "dminvoicemanager.db";
	private SQLiteDatabase mydb;
	
	static Cursor selectInvHeadDetails = null;
	static Cursor selectInvDetDetails = null;

	static int invhedrec = 0;
	static int invdetrec = 0;
	TextView txtHeading;
	
	public void btnBack(View view) {
		Intent myIntent = new Intent(view.getContext(), DM_Excel_Generator.class);
		myIntent.addFlags(Intent.FLAG_ACTIVITY_CLEAR_TOP
				| Intent.FLAG_ACTIVITY_NEW_TASK);
		startActivityForResult(myIntent, 0);
		finish();
	}
	
	@Override
	protected void onCreate(Bundle savedInstanceState) {
		super.onCreate(savedInstanceState);
		setContentView(R.layout.dm_backup_screen);
		
		txtHeading = (TextView) findViewById(R.id.txtHeading);

		txtHeading.setText("Stock Adjustment Backup");

		pdfFileName = getAddDateTimeString().toString();
		pdfFileName = pdfFileName +"ADJ"+ ".xls";

		/*View writeExcelButton = findViewById(R.id.writeExcel);
		writeExcelButton.setOnClickListener(this);*/

		mydb = openOrCreateDatabase(DBNAME, Context.MODE_PRIVATE, null);
		// invoice header
		try {
			String query = "SELECT * from FAdjHed";
			selectInvHeadDetails = mydb.rawQuery(query, null);
		} catch (Exception e) {
		}
		// invoice header
		try {
			String query = "SELECT * from FAdjDet";
			selectInvDetDetails = mydb.rawQuery(query, null);
		} catch (Exception e) {
		}
		saveExcelFile(this,pdfFileName);
	}
	
	private static boolean saveExcelFile(Context context, String fileName) { 
   	 
        // check if available and not read only 
        if (!isExternalStorageAvailable() || isExternalStorageReadOnly()) { 
            Log.w("FileUtils", "Storage not available or read only"); 
            return false; 
        } 
 
        boolean success = false; 
 
        //New Workbook
        Workbook wb = new HSSFWorkbook();
 
        Cell c = null;
 
        //Cell style for header row
        CellStyle cs = wb.createCellStyle();
        cs.setFillForegroundColor(HSSFColor.LIME.index);
        cs.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        
        CellStyle csdata = wb.createCellStyle();
        csdata.setFillForegroundColor(HSSFColor.SKY_BLUE.index);
        csdata.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
       
        
        //*************SHEET 1****************
        //New Sheet fInvHed
        Sheet sheet1 = null;
        sheet1 = wb.createSheet("FAdjHed");
 
        // Generate column headings
        Row row = sheet1.createRow(0);
 
		c = row.createCell(0);c.setCellValue("fadjhed_id");c.setCellStyle(cs);
		c = row.createCell(1);c.setCellValue("RefNo");c.setCellStyle(cs);
		c = row.createCell(2);c.setCellValue("TxnDate");c.setCellStyle(cs);
		c = row.createCell(3);c.setCellValue("CostCode");c.setCellStyle(cs);
		c = row.createCell(4);c.setCellValue("ManuRef");c.setCellStyle(cs);
		c = row.createCell(5);c.setCellValue("DealCode");c.setCellStyle(cs);
		c = row.createCell(6);c.setCellValue("RefNo2");c.setCellStyle(cs);
		c = row.createCell(7);c.setCellValue("TxnType");c.setCellStyle(cs);
		c = row.createCell(8);c.setCellValue("TotalAmt");c.setCellStyle(cs);
		c = row.createCell(9);c.setCellValue("Remarks");c.setCellStyle(cs);
		c = row.createCell(10);c.setCellValue("LocCode");c.setCellStyle(cs);
		c = row.createCell(11);c.setCellValue("ReaCode");c.setCellStyle(cs);
		c = row.createCell(12);c.setCellValue("GlPost");c.setCellStyle(cs);
		c = row.createCell(13);c.setCellValue("GlBatch");c.setCellStyle(cs);
		c = row.createCell(14);c.setCellValue("AddUser");c.setCellStyle(cs);
		c = row.createCell(15);c.setCellValue("AddDate");c.setCellStyle(cs);
		c = row.createCell(16);c.setCellValue("AddMach");c.setCellStyle(cs);
		c = row.createCell(17);c.setCellValue("ItemType");c.setCellStyle(cs);
		c = row.createCell(18);c.setCellValue("adjHedSync");c.setCellStyle(cs);
		
        sheet1.setColumnWidth(0, (15 * 200));
        sheet1.setColumnWidth(1, (15 * 200));
        sheet1.setColumnWidth(2, (15 * 200));
        sheet1.setColumnWidth(3, (15 * 200));
        sheet1.setColumnWidth(4, (15 * 200));
        sheet1.setColumnWidth(5, (15 * 200));
        sheet1.setColumnWidth(6, (15 * 200));
        sheet1.setColumnWidth(7, (15 * 200));
        sheet1.setColumnWidth(8, (15 * 200));
        sheet1.setColumnWidth(9, (15 * 200));
        sheet1.setColumnWidth(10, (15 * 200));
        sheet1.setColumnWidth(12, (15 * 200));
        sheet1.setColumnWidth(13, (15 * 200));
        sheet1.setColumnWidth(14, (15 * 200));
        sheet1.setColumnWidth(15, (15 * 200));
        sheet1.setColumnWidth(16, (15 * 200));
        sheet1.setColumnWidth(17, (15 * 200));
        sheet1.setColumnWidth(18, (15 * 200));
        
        
        //*************SHEET 2****************
        //New Sheet fInvDet
        Sheet sheet2 = null;
        sheet2 = wb.createSheet("FAdjDet");
 
        // Generate column headings
        Row row3 = sheet2.createRow(0);
 
		c = row3.createCell(0);c.setCellValue("fadjdet_id");c.setCellStyle(cs);
		c = row3.createCell(1);c.setCellValue("RefNo");c.setCellStyle(cs);
		c = row3.createCell(2);c.setCellValue("TxnDate");c.setCellStyle(cs);
		c = row3.createCell(3);c.setCellValue("TxnType");c.setCellStyle(cs);
		c = row3.createCell(4);c.setCellValue("SeqNo");c.setCellStyle(cs);
		c = row3.createCell(5);c.setCellValue("ItemCode");c.setCellStyle(cs);
		c = row3.createCell(6);c.setCellValue("CaseQty");c.setCellStyle(cs);
		c = row3.createCell(7);c.setCellValue("PiceQty");c.setCellStyle(cs);
		c = row3.createCell(8);c.setCellValue("Qty");c.setCellStyle(cs);
		c = row3.createCell(9);c.setCellValue("CostPrice");c.setCellStyle(cs);
		c = row3.createCell(10);c.setCellValue("Amt");c.setCellStyle(cs);
		c = row3.createCell(11);c.setCellValue("ReaCode");c.setCellStyle(cs);
		c = row3.createCell(12);c.setCellValue("ItemName");c.setCellStyle(cs);
		c = row3.createCell(13);c.setCellValue("QOH");c.setCellStyle(cs);
		c = row3.createCell(14);c.setCellValue("ReaName");c.setCellStyle(cs);
		c = row3.createCell(15);c.setCellValue("CaseUnits");c.setCellStyle(cs);
		c = row3.createCell(16);c.setCellValue("CasePrice");c.setCellStyle(cs);
		c = row3.createCell(17);c.setCellValue("adjDetSync");c.setCellStyle(cs);
 
		sheet2.setColumnWidth(0, (15 * 200));
        sheet2.setColumnWidth(1, (15 * 200));
        sheet2.setColumnWidth(2, (15 * 200));
        sheet2.setColumnWidth(3, (15 * 200));
        sheet2.setColumnWidth(4, (15 * 200));
        sheet2.setColumnWidth(5, (15 * 200));
        sheet2.setColumnWidth(6, (15 * 200));
        sheet2.setColumnWidth(7, (15 * 200));
        sheet2.setColumnWidth(8, (15 * 200));
        sheet2.setColumnWidth(9, (15 * 200));
        sheet2.setColumnWidth(10, (15 * 200));
        sheet2.setColumnWidth(12, (15 * 200));
        sheet2.setColumnWidth(13, (15 * 200));
        sheet2.setColumnWidth(14, (15 * 200));
        sheet2.setColumnWidth(15, (15 * 200));
        sheet2.setColumnWidth(16, (15 * 200));
        sheet2.setColumnWidth(17, (15 * 200));
       
 
        // Create a path where we will place our List of objects on external storage 
        File file = new File(context.getExternalFilesDir(null), fileName); 
        FileOutputStream os = null; 
        
        try { 
            os = new FileOutputStream(file);
            //*****Print Sheet fInvHed Details*****
            if (selectInvHeadDetails != null) {
				if (selectInvHeadDetails.getCount() > 0) {
					while (selectInvHeadDetails.moveToNext()) {
				        String fadjhed_id = selectInvHeadDetails.getString(0);
						String RefNo = selectInvHeadDetails.getString(1);
						String TxnDate = selectInvHeadDetails.getString(2);
						String CostCode = selectInvHeadDetails.getString(3);
						String ManuRef = selectInvHeadDetails.getString(4);
						String DealCode = selectInvHeadDetails.getString(5);
						String RefNo2 = selectInvHeadDetails.getString(6);
						String TxnType = selectInvHeadDetails.getString(7);
						String TotalAmt = selectInvHeadDetails.getString(8);
						String Remarks = selectInvHeadDetails.getString(9);
						String LocCode = selectInvHeadDetails.getString(10);
						String ReaCode = selectInvHeadDetails.getString(11);
						String GlPost = selectInvHeadDetails.getString(12);
						String GlBatch = selectInvHeadDetails.getString(13);
						String AddUser = selectInvHeadDetails.getString(14);
						String AddDate = selectInvHeadDetails.getString(15);
						String AddMach = selectInvHeadDetails.getString(16);
						String ItemType = selectInvHeadDetails.getString(17);
						String adjHedSync = selectInvHeadDetails.getString(18);
						
						invhedrec = invhedrec +1;
						Row row1 = sheet1.createRow(invhedrec);
						
						c = row1.createCell(0);c.setCellValue(fadjhed_id);c.setCellStyle(cs);
						c = row1.createCell(1);c.setCellValue(RefNo);c.setCellStyle(cs);
						c = row1.createCell(2);c.setCellValue(TxnDate);c.setCellStyle(cs);
						c = row1.createCell(3);c.setCellValue(CostCode);c.setCellStyle(cs);
						c = row1.createCell(4);c.setCellValue(ManuRef);c.setCellStyle(cs);
						c = row1.createCell(5);c.setCellValue(DealCode);c.setCellStyle(cs);
						c = row1.createCell(6);c.setCellValue(RefNo2);c.setCellStyle(cs);
						c = row1.createCell(7);c.setCellValue(TxnType);c.setCellStyle(cs);
						c = row1.createCell(8);c.setCellValue(TotalAmt);c.setCellStyle(cs);
						c = row1.createCell(9);c.setCellValue(Remarks);c.setCellStyle(cs);
						c = row1.createCell(10);c.setCellValue(LocCode);c.setCellStyle(cs);
						c = row1.createCell(11);c.setCellValue(ReaCode);c.setCellStyle(cs);
						c = row1.createCell(12);c.setCellValue(GlPost);c.setCellStyle(cs);
						c = row1.createCell(13);c.setCellValue(GlBatch);c.setCellStyle(cs);
						c = row1.createCell(14);c.setCellValue(AddUser);c.setCellStyle(cs);
						c = row1.createCell(15);c.setCellValue(AddDate);c.setCellStyle(cs);
						c = row1.createCell(16);c.setCellValue(AddMach);c.setCellStyle(cs);
						c = row1.createCell(17);c.setCellValue(ItemType);c.setCellStyle(cs);
						c = row1.createCell(18);c.setCellValue(adjHedSync);c.setCellStyle(cs);
						
				        sheet1.setColumnWidth(0, (15 * 200));
				        sheet1.setColumnWidth(1, (15 * 200));
				        sheet1.setColumnWidth(2, (15 * 200));
				        sheet1.setColumnWidth(3, (15 * 200));
				        sheet1.setColumnWidth(4, (15 * 200));
				        sheet1.setColumnWidth(5, (15 * 200));
				        sheet1.setColumnWidth(6, (15 * 200));
				        sheet1.setColumnWidth(7, (15 * 200));
				        sheet1.setColumnWidth(8, (15 * 200));
				        sheet1.setColumnWidth(9, (15 * 200));
				        sheet1.setColumnWidth(10, (15 * 200));
				        sheet1.setColumnWidth(12, (15 * 200));
				        sheet1.setColumnWidth(13, (15 * 200));
				        sheet1.setColumnWidth(14, (15 * 200));
				        sheet1.setColumnWidth(15, (15 * 200));
				        sheet1.setColumnWidth(16, (15 * 200));
				        sheet1.setColumnWidth(17, (15 * 200));
				        sheet1.setColumnWidth(18, (15 * 200));
				  
					}
				}
            }
            //*****Print Sheet fInvDet Details*****
            if (selectInvDetDetails != null) {
				if (selectInvDetDetails.getCount() > 0) {
					while (selectInvDetDetails.moveToNext()) {
						String fadjdet_id = selectInvDetDetails.getString(0);
						String RefNo = selectInvDetDetails.getString(1);
						String TxnDate = selectInvDetDetails.getString(2);
						String TxnType = selectInvDetDetails.getString(3);
						String SeqNo = selectInvDetDetails.getString(4);
						String ItemCode = selectInvDetDetails.getString(5);
						String CaseQty = selectInvDetDetails.getString(6);
						String PiceQty = selectInvDetDetails.getString(7);
						String Qty = selectInvDetDetails.getString(8);
						String CostPrice = selectInvDetDetails.getString(9);
						String Amt = selectInvDetDetails.getString(10);
						String ReaCode = selectInvDetDetails.getString(11);
						String ItemName = selectInvDetDetails.getString(12);
						String QOH = selectInvDetDetails.getString(13);
						String ReaName = selectInvDetDetails.getString(14);
						String CaseUnits = selectInvDetDetails.getString(15);
						String CasePrice = selectInvDetDetails.getString(16);
						String adjDetSync = selectInvDetDetails.getString(17);

						invdetrec = invdetrec +1;
						Row row4 = sheet2.createRow(invdetrec);

						c = row4.createCell(0);c.setCellValue(fadjdet_id);c.setCellStyle(cs);
						c = row4.createCell(1);c.setCellValue(RefNo);c.setCellStyle(cs);
						c = row4.createCell(2);c.setCellValue(TxnDate);c.setCellStyle(cs);
						c = row4.createCell(3);c.setCellValue(TxnType);c.setCellStyle(cs);
						c = row4.createCell(4);c.setCellValue(SeqNo);c.setCellStyle(cs);
						c = row4.createCell(5);c.setCellValue(ItemCode);c.setCellStyle(cs);
						c = row4.createCell(6);c.setCellValue(CaseQty);c.setCellStyle(cs);
						c = row4.createCell(7);c.setCellValue(PiceQty);c.setCellStyle(cs);
						c = row4.createCell(8);c.setCellValue(Qty);c.setCellStyle(cs);
						c = row4.createCell(9);c.setCellValue(CostPrice);c.setCellStyle(cs);
						c = row4.createCell(10);c.setCellValue(Amt);c.setCellStyle(cs);
						c = row4.createCell(11);c.setCellValue(ReaCode);c.setCellStyle(cs);
						c = row4.createCell(12);c.setCellValue(ItemName);c.setCellStyle(cs);
						c = row4.createCell(13);c.setCellValue(QOH);c.setCellStyle(cs);
						c = row4.createCell(14);c.setCellValue(ReaName);c.setCellStyle(cs);
						c = row4.createCell(15);c.setCellValue(CaseUnits);c.setCellStyle(cs);
						c = row4.createCell(16);c.setCellValue(CasePrice);c.setCellStyle(cs);
						c = row4.createCell(17);c.setCellValue(adjDetSync);c.setCellStyle(cs);



						sheet2.setColumnWidth(0, (15 * 200));
						sheet2.setColumnWidth(1, (15 * 200));
						sheet2.setColumnWidth(2, (15 * 200));
						sheet2.setColumnWidth(3, (15 * 200));
						sheet2.setColumnWidth(4, (15 * 200));
						sheet2.setColumnWidth(5, (15 * 200));
						sheet2.setColumnWidth(6, (15 * 200));
						sheet2.setColumnWidth(7, (15 * 200));
						sheet2.setColumnWidth(8, (15 * 200));
						sheet2.setColumnWidth(9, (15 * 200));
						sheet2.setColumnWidth(10, (15 * 200));
						sheet2.setColumnWidth(12, (15 * 200));
						sheet2.setColumnWidth(13, (15 * 200));
						sheet2.setColumnWidth(14, (15 * 200));
						sheet2.setColumnWidth(15, (15 * 200));
						sheet2.setColumnWidth(16, (15 * 200));
						sheet2.setColumnWidth(17, (15 * 200));
						
					}
				}
            }
            
            //-----------------sheet 2---------------------

            wb.write(os);

            
            Log.w("FileUtils", "Writing file" + file); 
            success = true; 
        } catch (IOException e) { 
            Log.w("FileUtils", "Error writing " + file, e); 
        } catch (Exception e) { 
            Log.w("FileUtils", "Failed to save file", e); 
        } finally { 
            try { 
                if (null != os) 
                    os.close(); 
            } catch (Exception ex) { 
            } 
        } 
        Log.v(TAG, "BackupCompleted");
        return success; 
    } 
 
    public static boolean isExternalStorageReadOnly() { 
        String extStorageState = Environment.getExternalStorageState(); 
        if (Environment.MEDIA_MOUNTED_READ_ONLY.equals(extStorageState)) { 
            return true; 
        } 
        return false; 
    } 
 
    public static boolean isExternalStorageAvailable() { 
        String extStorageState = Environment.getExternalStorageState(); 
        if (Environment.MEDIA_MOUNTED.equals(extStorageState)) { 
            return true; 
        } 
        return false; 
    } 
    

	private String getAddDateTimeString() {
		Calendar cad = Calendar.getInstance();
		SimpleDateFormat dfad = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
		String formattedAddDate = dfad.format(cad.getTime());
		return formattedAddDate;
	}
	@Override
	public void onBackPressed() {
		// Do Here what ever you want do on back press;
	}
}
